package com.hy.dao;

import com.hy.model.Student;
import com.hy.util.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * StudentDAO
 * <p>
 * Created by yoka24443 on 2018-4-8.
 */
public class StudentDAO implements IStudentDAO<Student> {
    /**
     * 获取学生列表
     *
     * @return List
     */
    @Override
    public List<Student> getList() {
        Student model = null;
        return queryList(model);
    }

    /**
     * 查询学生信息
     *
     * @param queryModel Student
     * @return List
     */
    @Override
    public List<Student> queryList(Student queryModel) {
        List<Student> list = new ArrayList<Student>();
        StringBuilder sql = new StringBuilder();

        sql.append("select * from student where 1=1");

        if(queryModel != null) {
            if (queryModel.getStudentNO() != null && queryModel.getStudentNO().trim().length() > 0) {
                sql.append(" and studentNO = '").append(queryModel.getStudentNO()).append("'");
            }

            if (queryModel.getRealName() != null && queryModel.getRealName().trim().length() > 0) {
                sql.append(" and realName like '%").append(queryModel.getRealName()).append("%'");
            }

            if (queryModel.getClassName() != null && queryModel.getClassName().trim().length() > 0) {
                sql.append(" and className like '%").append(queryModel.getClassName()).append("%'");
            }

            if (queryModel.getAge() != null) {
                sql.append(" and age = ").append(queryModel.getAge());
            }

            if(queryModel.getTel() != null && queryModel.getTel().trim().length() > 0) {
                sql.append(" and tel like '%").append(queryModel.getTel()).append("%'");
            }

            if (queryModel.getAddress() != null && queryModel.getAddress().trim().length() > 0) {
                sql.append(" and address like '%").append(queryModel.getAddress()).append("%'");
            }

            if (queryModel.getWechat() != null && queryModel.getWechat().trim().length() > 0) {
                sql.append(" and wechat like '%").append(queryModel.getWechat()).append("%'");
            }

            if (queryModel.getQq() != null && queryModel.getQq().trim().length() > 0) {
                sql.append(" and qq like '%").append(queryModel.getQq()).append("%'");
            }
        }

        ResultSet rs = DBUtil.executeSql(sql.toString());

        if (rs != null) {
            try {
                Student model;
                while (rs.next()) {
                    model = new Student();
                    model.setStudentNO(rs.getString("studentNO"));
                    model.setRealName(rs.getString("realName"));
                    model.setClassName(rs.getString("className"));
                    model.setAge(rs.getInt("age"));
                    model.setTel(rs.getString("tel"));
                    model.setAddress(rs.getString("address"));
                    model.setWechat(rs.getString("wechat"));
                    model.setQq(rs.getString("qq"));

                    list.add(model);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBUtil.close();
            }
        }

        return list;
    }

    /**
     * 查询学生信息
     *
     * @param params 参数
     * @return
     */
    @Override
    public List<Student> queryList(String... params) {
        return null;
    }

    /**
     * 按学号查找学生信息
     *
     * @param studentNO 学号
     * @return Student
     */
    @Override
    public Student findByStudentNO(String studentNO) {
        Student model = null;

        if (studentNO == null || studentNO.trim().length() == 0) {
            return model;
        }

        Student params = new Student();
        params.setStudentNO(studentNO);
        List<Student> list = queryList(params);

        if(list != null && list.size() > 0) {
            model = list.get(0);
        }

        return model;
    }

    /**
     * 按学生姓名查找学生信息
     *
     * @param studentName 学生姓名
     * @return List
     */
    @Override
    public List<Student> findByStudentName(String studentName) {
        List<Student> list;
        Student params = new Student();
        params.setRealName(studentName);

        list = queryList(params);

        return list;
    }

    /**
     * 添加学生信息
     *
     * @param model Student
     * @return boolean
     */
    @Override
    public boolean add(Student model) {
        int effectCount;
        StringBuilder sql = new StringBuilder();

        sql.append("insert into student values(");

        sql.append("'").append(model.getStudentNO()).append("',");
        sql.append("'").append(model.getRealName()).append("',");
        sql.append("'").append(model.getClassName()).append("',");
        sql.append(model.getAge()).append(",");
        sql.append("'").append(model.getTel()).append("',");
        sql.append("'").append(model.getAddress()).append("',");
        sql.append("'").append(model.getWechat()).append("',");
        sql.append("'").append(model.getQq()).append("'");

        sql.append(")");

        effectCount = DBUtil.executeUpdate(sql.toString());

        if (effectCount > 0) {
            return true;
        } else {

            return false;
        }
    }

    /**
     * 更新学生信息
     *
     * @param model Student
     * @return boolean
     */
    @Override
    public boolean update(Student model) {
        int effectCount;
        StringBuilder sql = new StringBuilder();

        sql.append("update student set studentNO='").append(model.getStudentNO()).append("'");

        // 姓名
        if(model.getRealName() != null && model.getRealName().trim().length() > 0) {
            sql.append(", realName = '").append(model.getRealName()).append("'");
        }

        // 班级名称
        if(model.getClassName() != null && model.getClassName().trim().length() > 0) {
            sql.append(", className = '").append(model.getClassName()).append("'");
        }

        // 年龄
        if(model.getAge() != null) {
            sql.append(", age = ").append(model.getAge());
        }

        // 电话
        if(model.getTel() != null && model.getTel().trim().length() > 0) {
            sql.append(", tel = '").append(model.getTel()).append("'");
        }

        // 地址
        if(model.getAddress() != null && model.getAddress().trim().length() > 0) {
            sql.append(", address = '").append(model.getAddress()).append("'");
        }

        // 微信号
        if(model.getWechat() != null && model.getWechat().trim().length() > 0) {
            sql.append(", wechat = '").append(model.getWechat()).append("'");
        }

        // QQ
        if(model.getQq() != null && model.getQq().trim().length() > 0) {
            sql.append(", qq = '").append(model.getQq()).append("'");
        }

        sql.append(" where studentNO='").append(model.getStudentNO()).append("'");

        effectCount = DBUtil.executeUpdate(sql.toString());

        if (effectCount > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 删除学生信息
     *
     * @param studentNO 学号
     * @return boolean
     */
    @Override
    public boolean delete(String studentNO) {
        int effectCount;
        StringBuilder sql = new StringBuilder();

        sql.append("delete from student");
        sql.append(" where studentNO='").append(studentNO).append("'");

        effectCount = DBUtil.executeUpdate(sql.toString());

        if (effectCount > 0) {
            return true;
        } else {
            return false;
        }
    }
}
